package utils;

import com.alibaba.druid.pool.DruidDataSource;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.SerializationFeature;
import com.fasterxml.jackson.databind.node.ObjectNode;
import com.fasterxml.jackson.datatype.jsr310.JavaTimeModule;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.experimental.Accessors;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class JDBCUtils {
    private static DruidDataSource dataSource = null;
    private static ThreadLocal<Connection> connectionThreadLocal = new ThreadLocal<>();
    private static final Logger logger = LogManager.getLogger(JDBCUtils.class);
    private static final ObjectMapper objectMapper;
    private static final Pattern paramPattern = Pattern.compile("\\?");
    private static final DateTimeFormatter dateFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    // 静态初始化块
    static {
        // 初始化 ObjectMapper
        objectMapper = new ObjectMapper();
        objectMapper.registerModule(new JavaTimeModule());
        objectMapper.disable(SerializationFeature.WRITE_DATES_AS_TIMESTAMPS);

        logger.info("JDBCUtils 静态初始化完成，时间：{}",
                LocalDateTime.now().format(dateFormatter));
    }

    @Getter
    @Setter
    @NoArgsConstructor
    @AllArgsConstructor
    @Accessors(chain = true)
    public static class Page<T> {
        private int pageNum;    // 当前页码
        private int pageSize;   // 每页记录数
        private long total;     // 总记录数
        private int pages;      // 总页数
        private List<T> list;   // 数据列表

        public Page(int pageNum, int pageSize) {
            this.pageNum = pageNum;
            this.pageSize = pageSize;
        }

        public void setTotal(long total) {
            this.total = total;
            this.pages = (int) Math.ceil((double) total / pageSize);
        }

        public boolean hasNextPage() {
            return pageNum < pages;
        }

        public boolean hasPreviousPage() {
            return pageNum > 1;
        }
    }

    @FunctionalInterface
    public interface RowMapper<T> {
        T mapRow(ResultSet rs) throws SQLException;
    }

    private static void initializeDataSource() {
        if (dataSource == null) {
            synchronized (JDBCUtils.class) {
                if (dataSource == null) {
                    dataSource = new DruidDataSource();
                    dataSource.setUrl("jdbc:mysql://localhost:3306/web-movie");
                    dataSource.setUsername("root");
                    dataSource.setPassword("root");
                    dataSource.setInitialSize(5);
                    dataSource.setMinIdle(5);
                    dataSource.setMaxActive(20);
                    try {
                        dataSource.setFilters("stat");
                        logger.info("数据库连接池初始化完成，时间：{}",
                                LocalDateTime.now().format(dateFormatter));
                    } catch (SQLException e) {
                        logger.error("初始化Druid连接池失败，时间：{}",
                                LocalDateTime.now().format(dateFormatter), e);
                        throw new RuntimeException(e);
                    }
                }
            }
        }
    }

    public static Connection getConnection() {
        Connection conn = connectionThreadLocal.get();
        if (conn == null) {
            if (dataSource == null) {
                initializeDataSource();
            }
            try {
                conn = dataSource.getConnection();
                connectionThreadLocal.set(conn);
                logger.debug("新建数据库连接，时间：{}",
                        LocalDateTime.now().format(dateFormatter));
            } catch (SQLException e) {
                logger.error("获取数据库连接失败，时间：{}",
                        LocalDateTime.now().format(dateFormatter), e);
                throw new RuntimeException("获取数据库连接失败", e);
            }
        }
        return conn;
    }

    public static void closeConnection() {
        Connection conn = connectionThreadLocal.get();
        if (conn != null) {
            try {
                conn.close();
                logger.debug("数据库连接已关闭，时间：{}",
                        LocalDateTime.now().format(dateFormatter));
            } catch (SQLException e) {
                logger.error("关闭数据库连接失败，时间：{}",
                        LocalDateTime.now().format(dateFormatter), e);
            } finally {
                connectionThreadLocal.remove();
            }
        }
    }

    public static Object execute(String sql, Object... params) throws SQLException {
        LocalDateTime startTime = LocalDateTime.now();
        String formattedStartTime = startTime.format(dateFormatter);
        Connection connection = getConnection();

        ObjectNode logEntry = objectMapper.createObjectNode();
        logEntry.put("prePareStatementSql", sql);
        logEntry.put("startTime", formattedStartTime);
        logEntry.set("params", objectMapper.valueToTree(createParamMap(sql, params)));

        PreparedStatement stmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        for (int i = 0; i < params.length; i++) {
            stmt.setObject(i + 1, params[i]);
        }

        boolean isQuery = sql.trim().toLowerCase().startsWith("select");
        String actualSql = replaceSqlParams(sql, params);
        Object result;

        if (isQuery) {
            result = stmt.executeQuery();
            logEntry.put("type", "query");
        } else {
            result = stmt.executeUpdate();
            logEntry.put("type", "update");
            logEntry.put("affectedRows", (Integer)result);
        }

        LocalDateTime endTime = LocalDateTime.now();
        logEntry.put("actualSql", actualSql);
        logEntry.put("endTime", endTime.format(dateFormatter));
        logEntry.put("executionTimeMs",
                java.time.Duration.between(startTime, endTime).toMillis());

        logger.info("SQL执行日志: {}", logEntry);
        return result;
    }

    public static <T> Page<T> executePageQuery(String sql, Page<T> page,
                                               RowMapper<T> rowMapper, Object... params) throws SQLException {
        try {
            String countSql = "SELECT COUNT(*) FROM (" + sql + ") tmp";
            ResultSet countRs = (ResultSet) execute(countSql, params);
            if (countRs.next()) {
                page.setTotal(countRs.getLong(1));
            }
            countRs.close();

            if (page.getTotal() == 0) {
                page.setList(new ArrayList<>());
                return page;
            }

            String pageSql = sql + " LIMIT ?, ?";
            Object[] pageParams = Arrays.copyOf(params, params.length + 2);
            pageParams[params.length] = (page.getPageNum() - 1) * page.getPageSize();
            pageParams[params.length + 1] = page.getPageSize();

            ResultSet rs = (ResultSet) execute(pageSql, pageParams);
            List<T> list = new ArrayList<>();
            while (rs.next()) {
                list.add(rowMapper.mapRow(rs));
            }
            rs.close();
            page.setList(list);
            return page;
        } catch (SQLException e) {
            logger.error("分页查询失败: ", e);
            throw new SQLException("执行分页查询失败: " + e.getMessage(), e);
        }
    }

    private static Map<String, Object> createParamMap(String sql, Object[] params) {
        Map<String, Object> paramMap = new LinkedHashMap<>();
        Matcher matcher = paramPattern.matcher(sql);
        int paramIndex = 0;

        while (matcher.find() && paramIndex < params.length) {
            int start = Math.max(0, matcher.start() - 20);
            int end = Math.min(sql.length(), matcher.end() + 20);
            String context = sql.substring(start, end);
            String paramName = extractParamName(context);
            String key = paramName != null ?
                    String.format("%s (参数%d)", paramName, paramIndex + 1) :
                    String.format("参数%d", paramIndex + 1);

            Object value = params[paramIndex];
            // 处理 LocalDateTime 类型
            if (value instanceof LocalDateTime) {
                value = ((LocalDateTime) value).format(dateFormatter);
            }

            paramMap.put(key, value != null ? value : "null");
            paramIndex++;
        }
        return paramMap;
    }

    private static String extractParamName(String context) {
        Pattern pattern = Pattern.compile(
                "(WHERE|AND|OR|SET|INSERT|UPDATE|DELETE)\\s+([\\w_]+)\\s*=\\s*\\?",
                Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(context);
        return matcher.find() ? matcher.group(2) : null;
    }

    private static String replaceSqlParams(String sql, Object[] params) {
        StringBuilder result = new StringBuilder(sql);
        int offset = 0;

        for (int i = 0; i < params.length; i++) {
            int questionMarkIndex = result.indexOf("?", offset);
            if (questionMarkIndex == -1) break;

            String paramValue;
            if (params[i] == null) {
                paramValue = "NULL";
            } else if (params[i] instanceof String || params[i] instanceof Date
                    || params[i] instanceof LocalDateTime) {
                paramValue = "'" + params[i] + "'";
            } else {
                paramValue = params[i].toString();
            }

            result.replace(questionMarkIndex, questionMarkIndex + 1, paramValue);
            offset = questionMarkIndex + paramValue.length();
        }
        return result.toString();
    }
}

